Maintaining a secure environment is very hard. There are so many threats that can be exploited that it demands a specialized security team to continuously evaluate, monitor, and audit the many known and unknown threats. SQL Server is just another process that can be exploited and needs to be monitored. Still, since the database’s nature is to store information, including sensitive information, it is one of the main targets chosen by attackers.
In this article, I would like to show you a technique that can be used to reveal information that a user is not supposed to see and how to protect it. The technique is very simple, and it relies on SQL Server error messages to reveal information that could be stolen by adversaries. This technique has been used for several years in SQL Injection attacks, and many DBAs still overlook it.
The idea is simple; a non-privileged user can write a query referencing a SQL Server view that causes an exception, such as invalid conversion to be thrown during query processing if certain row values exist in the underlying tables. Depending on the query plan, these exceptions may bypass SQL Server permission validations and are thrown even if existing data cannot be retrieved through the view.
Before I move forward with recommendations, let’s understand the problem starting by looking at a simple example.
Note: I’m running all tests on Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) – 14.0.3294.2 (X64) . Other versions may have different results.
A simple view-based row-level security
Before SQL Server 2016 and row-level-security, it was very difficult to implement restrictions on data at the row level. One of the most common solutions for this requirement is to use a view with a predicate filter used to reveal only the information a user has access.
For instance, consider the following scenario:
Create two user accounts that will demonstrate different access capabilities.
|
1 2 3 4 5 6 7 |
USE tempdb GO DROP USER IF EXISTS Manager DROP USER IF EXISTS User1 CREATE USER Manager WITHOUT LOGIN CREATE USER User1 WITHOUT LOGIN GO |
Create a table to hold test data.
|
1 2 3 4 5 6 7 8 9 |
DROP TABLE IF EXISTS TabSalary CREATE TABLE TabSalary ( EmpID INT, Employee VARCHAR(10), Salary NUMERIC(8,2), HideSalary BIT ); GO |
Populate the table with four rows of data.
|
1 2 3 4 5 6 7 8 |
INSERT INTO TabSalary VALUES(1, 'Bob', 1000, 0), (2, 'Jonh', 5000, 0), (3, 'Mark', 8000, 1), (4, 'Robert', 9500, 1) GO -- 4 rows... SELECT * FROM TabSalary GO |
A row-level security view controls which rows each user can see. For instance, the Manager user will be able to see all rows, while any other user will only see rows where HideSalary is equal to 0.
|
1 2 3 4 5 6 7 |
DROP VIEW IF EXISTS vw_LowSalary GO CREATE VIEW vw_LowSalary AS SELECT EmpID, Employee, Salary FROM TabSalary WHERE (HideSalary = 0 OR USER_NAME() = 'Manager') GO |
Grant read access on the view to users.
|
1 2 3 |
GRANT SELECT ON vw_LowSalary TO Manager GRANT SELECT ON vw_LowSalary TO User1 GO |
Now, if User1 tries to access the data, it will return the following:
|
1 2 3 4 5 |
EXECUTE AS USER = 'User1' SELECT * FROM vw_LowSalary GO REVERT GO |
User Manager will have access to all rows:
|
1 2 3 4 5 |
EXECUTE AS USER = 'Manager' SELECT * FROM vw_LowSalary GO REVERT GO |
The problem with this approach is that any user with read access to the view can write a carefully crafted query that uses an expression executed in a specific order by the query optimizer. This causes an information leak through the use of the exception error message.
For instance, if a query that attempts to convert the Employee column to an Integer, it returns the following message:
|
1 2 3 4 5 6 |
EXECUTE AS USER = 'User1' SELECT * FROM vw_LowSalary WHERE CONVERT(INT, Employee) = 1 GO REVERT GO |
Bob is a name that User1 could already access, so there is no “leaked information” here. But what about a query that ignores employees Bob and Jonh that User1 can already access?
|
1 2 3 4 5 6 7 |
EXECUTE AS USER = 'User1' SELECT * FROM vw_LowSalary WHERE Employee NOT IN ('Bob', 'Jonh') AND CONVERT(INT, Employee) = 1 GO REVERT GO |
User1 shouldn’t be able to see Mark’s row, right? How can User1 see Marks salary information? Easy, just change the query to convert the salary column.
|
1 2 3 4 5 6 7 |
EXECUTE AS USER = 'User1' SELECT * FROM vw_LowSalary WHERE Employee NOT IN ('Bob', 'Jonh') AND CONVERT(INT, CONVERT(VARCHAR, Salary)) = 0 GO REVERT GO |
As you can see, even though there is a security predicate in place to prevent a malicious user from directly querying other people’s salary, User1 was able to determine the data by running a query that returns a SQL Server error message to leak the data.
Note: To leak the correct information, you need to make sure that the query processor is evaluating the expression in the correct order. Otherwise, it would first try to convert the data in the row the user already has access to and not leak the desired data. The query optimizer will have to create a plan that is pushing the predicate down to the table access. Look at the execution plan of the query to confirm the expression evaluation order. If necessary, you may need to force a short-circuit using a CASE expression.
Row-level security in SQL Server 2016 was introduced to address this problem. Well, kind of. Considering the same scenario, you would need to do the following:
The first step is to create a function with the predicate that will be used in a security policy.
|
1 2 3 4 5 6 7 8 9 10 11 |
DROP SECURITY POLICY IF EXISTS SalaryFilter GO DROP FUNCTION IF EXISTS dbo.fn_SecurityPredicate GO CREATE FUNCTION dbo.fn_SecurityPredicate(@HideSalary CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE (@HideSalary = 0 OR USER_NAME() = 'Manager'); GO |
Create a security policy on table TabSalary.
|
1 2 3 4 5 |
CREATE SECURITY POLICY SalaryFilter ADD FILTER PREDICATE dbo.fn_SecurityPredicate(HideSalary) ON dbo.TabSalary WITH (STATE = ON); GO |
Grant access to table TabSalary to users.
|
1 2 3 |
GRANT SELECT ON TabSalary TO Manager; GRANT SELECT ON TabSalary TO User1; GO |
Just like when using the view, User1 only has access to employees “Bob” and “Jonh”.
|
1 2 3 4 5 |
EXECUTE AS USER = 'User1' SELECT * FROM TabSalary GO REVERT GO |
The user Manager can see all rows.
|
1 2 3 4 5 |
EXECUTE AS USER = 'Manager' SELECT * FROM TabSalary GO REVERT GO |
Here is the main difference between the view-based solution and the row-level security feature. If User1 tries to run the implicit conversion query, the query will return the following error message:
|
1 2 3 4 5 6 7 |
EXECUTE AS USER = 'User1' SELECT * FROM TabSalary WHERE Employee NOT IN ('Bob', 'Jonh') AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0 GO REVERT GO |
As you can see, the data is masked. Because the query failed, the attacker can still infer that there are more rows in the underlying data. They can even write a query to identify the salary value, but it will be more difficult, and the data will never be leaked to the user screen. A query to infer the salary would be something like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
EXECUTE AS USER = 'User1' GO SELECT * FROM TabSalary WHERE Employee NOT IN ('Bob', 'Jonh') AND Salary = 7999 AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0 GO SELECT * FROM TabSalary WHERE Employee NOT IN ('Bob', 'Jonh') AND Salary = 8000 AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0 GO SELECT * FROM TabSalary WHERE Employee NOT IN ('Bob', 'Jonh') AND Salary = 8001 AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0 GO REVERT GO |
As you can see, a user could write a query in a loop to test a range of values and infer that a value exists. It is limited information, but in some scenarios, this could be a security problem.
Some recommendations to minimize the risk:
- Consider using the row-level security feature to minimize the information exposed to the attacker.
- View-based level security should not be used as an isolated measure to fully secure sensitive data from users running ad-hoc queries on the database. It is appropriate for preventing sensitive data exposure but will not protect against malicious users trying to infer or reveal the underlying data.
- Any SQL Server or associated application providing too much information in error messages on the screen or printout risks compromising the data and security of the system. The structure and content of error messages need to be carefully considered by the organization and development team.
- Databases can inadvertently provide a wealth of information to an attacker through improperly handled error messages. In addition to sensitive business or personal information, database errors can provide hostnames, IP addresses, user names, and other system information not required for end-user troubleshooting but very useful to someone targeting the system.
- Detailed error messages must be visible only to those who are authorized to view them. General users must receive only generalized acknowledgments that errors have occurred. These generalized messages must appear only when relevant to the user’s task.
- Configure audit logging, tracing or custom code in the database or application to record detailed error messages generated by SQL Server for review by authorized personnel.
- Consider enabling trace flag 3625 to mask certain system-level error information returned to non-administrative users.
- Inspect application source code, which will require collaboration with the application developers. It is recognized that, in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered.
Final thoughts
It is important to understand that this approach to secure data using row level views is not very safe. Any arbitrary T-SQL and access to all views (including system views) should be limited to only specific users.
One other thing I like to do in my environments is to get rid of any permissions granted to the “public” role. You know, by default, SQL Server system objects have permissions granted to public. That means any login will automatically have access to those tables. But do they really need it? I don’t think so. Of course, you’ll have to test it to confirm, but many applications never use system objects. You may want to remove public access to all system objects. Following is a link with a Microsoft article that will help you with that: https://techcommunity.microsoft.com/t5/sql-server/remove-public-and-guest-permissions/ba-p/383594
Sensitive environments require special attention to many things, including application users that may look harmless. Remember, most attacks come from inside; that means any user that already has access to the system should be limited to do only what is intended to do and nothing else. Deny everything and grant access as you go (developers will hate me for that 😊).
It would be nice if there were an option on SQL Server to limit the information returned in an error message. In my opinion, this is the primary security issue here which has been exploited for many years with SQL Injection. Something like TF3625 “Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages using ‘******’. This can help prevent disclosure of sensitive information.”.
Am I too cautious? What do you think?
Load comments